import express from 'express';
import { body, validationResult } from 'express-validator';
import { executeQuery, getOne, transaction } from '../config/database.js';
import { authenticateToken, requireTeacher, requireDriver } from '../middleware/auth.js';
import { emitStudentStatusUpdate, emitAttendanceUpdate } from '../utils/socketService.js';
import { getAttendanceSetting, isAfterModificationLimit, isAfterArrivalTime } from '../utils/attendanceSettings.js';

const router = express.Router();

// 获取考勤记录
router.get('/', authenticateToken, async (req, res) => {
  try {
    const { 
      date = new Date().toISOString().split('T')[0], 
      classId, 
      studentId,
      status,
      page = 1, 
      limit = 50 
    } = req.query;
    
    const offset = (page - 1) * limit;
    const userRole = req.user.role;

    let whereClause = 'WHERE attendance_date = ?';
    let params = [date];

    // 教师只能查看自己负责的班级
    if (userRole === 'teacher') {
      const teacherClasses = await executeQuery(`
        SELECT class_id FROM teacher_classes WHERE teacher_id = ?
      `, [req.user.id]);

      if (teacherClasses.length === 0) {
        return res.json({
          success: true,
          data: { records: [], pagination: { page: 1, limit, total: 0, pages: 0 } }
        });
      }

      const classIds = teacherClasses.map(tc => tc.class_id);
      whereClause += ` AND class_id IN (${classIds.map(() => '?').join(',')})`;
      params.push(...classIds);
    }

    // 添加其他筛选条件
    if (classId) {
      whereClause += ' AND class_id = ?';
      params.push(classId);
    }

    if (studentId) {
      whereClause += ' AND student_id = ?';
      params.push(studentId);
    }

    if (status) {
      whereClause += ' AND arrival_status = ?';
      params.push(status);
    }

    const records = await executeQuery(`
      SELECT 
        ar.id, ar.student_id, ar.attendance_date,
        ar.arrival_status, ar.arrival_timestamp,
        ar.departure_status, ar.departure_method, ar.departure_timestamp,
        ar.notes, ar.created_at, ar.updated_at,
        s.student_no, s.name as student_name,
        c.name as class_name, c.grade,
        CASE 
          WHEN ar.arrival_timestamp IS NOT NULL AND ar.departure_timestamp IS NOT NULL 
          THEN TIMESTAMPDIFF(MINUTE, ar.arrival_timestamp, ar.departure_timestamp)
          ELSE NULL 
        END as duration_minutes
      FROM attendance_records ar
      INNER JOIN students s ON ar.student_id = s.id
      INNER JOIN classes c ON s.class_id = c.id
      ${whereClause}
      ORDER BY ar.attendance_date DESC, c.grade, c.name, s.student_no
      LIMIT ? OFFSET ?
    `, [...params, parseInt(limit), parseInt(offset)]);

    const totalCount = await getOne(`
      SELECT COUNT(*) as count
      FROM attendance_records ar
      INNER JOIN students s ON ar.student_id = s.id
      INNER JOIN classes c ON s.class_id = c.id
      ${whereClause}
    `, params);

    res.json({
      success: true,
      data: {
        records,
        pagination: {
          page: parseInt(page),
          limit: parseInt(limit),
          total: totalCount.count,
          pages: Math.ceil(totalCount.count / limit)
        }
      }
    });

  } catch (error) {
    console.error('获取考勤记录错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 入园接口
router.post('/arrival', [
  authenticateToken,
  requireTeacher,
  body('student_id').optional().isInt().withMessage('学生ID必须是整数'),
  body('qr_code').optional().trim().notEmpty().withMessage('二维码不能为空'),
  body('notes').optional().trim()
], async (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        message: '输入验证失败',
        errors: errors.array()
      });
    }

    const { student_id, qr_code, notes } = req.body;
    const date = new Date().toISOString().split('T')[0];
    const timestamp = new Date();

    let studentInfo;
    if (student_id) {
      studentInfo = await getOne(`
        SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
        FROM students s
        INNER JOIN classes c ON s.class_id = c.id
        WHERE s.id = ?
      `, [student_id]);
    } else if (qr_code) {
      studentInfo = await getOne(`
        SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
        FROM students s
        INNER JOIN classes c ON s.class_id = c.id
        WHERE s.qr_code = ?
      `, [qr_code]);
    } else {
      return res.status(400).json({
        success: false,
        message: '必须提供学生ID或二维码'
      });
    }

    if (!studentInfo) {
      return res.status(404).json({
        success: false,
        message: '学生不存在'
      });
    }

    // 教师权限检查
    const hasPermission = await getOne(`
      SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
    `, [req.user.id, studentInfo.class_id]);

    if (!hasPermission) {
      return res.status(403).json({
        success: false,
        message: '无权为该学生标记考勤'
      });
    }

    // 检查入园时间，如果超过入园时间，自动标记为异常
    const isLate = await isAfterArrivalTime();
    const arrivalStatus = isLate ? 'abnormal' : 'present';
    const arrivalTime = await getAttendanceSetting('arrival_time');
    
    if (isLate) {
      return res.status(400).json({
        success: false,
        message: `超过入园时间(${arrivalTime})，学生已自动标记为异常状态`
      });
    }

    // 检查今日是否已有考勤记录
    const existingRecord = await getOne(`
      SELECT id, arrival_status FROM attendance_records 
      WHERE student_id = ? AND attendance_date = ?
    `, [studentInfo.id, date]);

    let recordId;
    if (existingRecord) {
      // 更新现有记录
      recordId = existingRecord.id;
      await executeQuery(`
        UPDATE attendance_records 
        SET arrival_status = ?, arrival_timestamp = ?, notes = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `, [arrivalStatus, timestamp, notes || null, recordId]);
    } else {
      // 创建新记录
      const result = await executeQuery(`
        INSERT INTO attendance_records (student_id, attendance_date, arrival_status, arrival_timestamp, notes)
        VALUES (?, ?, ?, ?, ?)
      `, [studentInfo.id, date, arrivalStatus, timestamp, notes || null]);
      recordId = result.insertId;
    }

    // 发送WebSocket通知
    emitStudentStatusUpdate({
      studentId: studentInfo.id,
      classId: studentInfo.class_id,
      status: 'present',
      timestamp: timestamp,
      type: 'arrival'
    });

    res.json({
      success: true,
      message: '入园记录成功',
      data: {
        student_id: studentInfo.id,
        student_name: studentInfo.name,
        class_name: studentInfo.class_name,
        timestamp: timestamp
      }
    });

  } catch (error) {
    console.error('入园记录错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 请假接口
router.post('/request-leave', [
  authenticateToken,
  requireTeacher,
  body('student_id').isInt().withMessage('学生ID必须是整数'),
  body('notes').optional().trim()
], async (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        message: '输入验证失败',
        errors: errors.array()
      });
    }

    const { student_id, notes } = req.body;
    const date = new Date().toISOString().split('T')[0];
    const timestamp = new Date();

    // 获取学生信息
    const studentInfo = await getOne(`
      SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      WHERE s.id = ?
    `, [student_id]);

    if (!studentInfo) {
      return res.status(404).json({
        success: false,
        message: '学生不存在'
      });
    }

    // 教师权限检查
    const hasPermission = await getOne(`
      SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
    `, [req.user.id, studentInfo.class_id]);

    if (!hasPermission) {
      return res.status(403).json({
        success: false,
        message: '无权为该学生标记考勤'
      });
    }

    // 检查今日是否已有考勤记录
    const existingRecord = await getOne(`
      SELECT id FROM attendance_records 
      WHERE student_id = ? AND attendance_date = ?
    `, [student_id, date]);

    let recordId;
    if (existingRecord) {
      // 更新现有记录为请假状态
      recordId = existingRecord.id;
      await executeQuery(`
        UPDATE attendance_records 
        SET arrival_status = 'leave', arrival_timestamp = NULL, notes = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `, [notes || null, recordId]);
    } else {
      // 创建新的请假记录
      const result = await executeQuery(`
        INSERT INTO attendance_records (student_id, attendance_date, arrival_status, notes)
        VALUES (?, ?, 'leave', ?)
      `, [student_id, date, notes || null]);
      recordId = result.insertId;
    }

    // 发送WebSocket通知
    emitStudentStatusUpdate({
      studentId: student_id,
      classId: studentInfo.class_id,
      status: 'leave',
      timestamp: timestamp,
      type: 'leave'
    });

    res.json({
      success: true,
      message: '请假记录成功',
      data: {
        student_id: student_id,
        student_name: studentInfo.name,
        class_name: studentInfo.class_name,
        timestamp: timestamp
      }
    });

  } catch (error) {
    console.error('请假记录错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 设置离校方式接口
router.post('/set-departure-method', [
  authenticateToken,
  requireTeacher,
  body('student_id').isInt().withMessage('学生ID必须是整数'),
  body('departure_method').isIn(['parent_pickup', 'bus', 'after_school', 'special_course']).withMessage('离校方式无效')
], async (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        message: '输入验证失败',
        errors: errors.array()
      });
    }

    const { student_id, departure_method } = req.body;
    const date = new Date().toISOString().split('T')[0];
    const timestamp = new Date();
    const currentHour = timestamp.getHours();
    const userRole = req.user.role;

    // 获取学生信息
    const studentInfo = await getOne(`
      SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      WHERE s.id = ?
    `, [student_id]);

    if (!studentInfo) {
      return res.status(404).json({
        success: false,
        message: '学生不存在'
      });
    }

    // 时间限制检查：使用动态设置的时间限制
    const isAfterLimit = await isAfterModificationLimit();
    if (isAfterLimit && userRole !== 'admin') {
      const limitTime = await getAttendanceSetting('modification_limit_time');
      return res.status(403).json({
        success: false,
        message: `${limitTime}后只有管理员可以更改离校方式`
      });
    }

    // 教师权限检查
    if (userRole === 'teacher') {
      const hasPermission = await getOne(`
        SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
      `, [req.user.id, studentInfo.class_id]);

      if (!hasPermission) {
        return res.status(403).json({
          success: false,
          message: '无权为该学生设置离校方式'
        });
      }
    }

    // 检查今日考勤记录
    const attendanceRecord = await getOne(`
      SELECT id, arrival_status FROM attendance_records 
      WHERE student_id = ? AND attendance_date = ?
    `, [student_id, date]);

    if (!attendanceRecord) {
      return res.status(400).json({
        success: false,
        message: '学生今日未有考勤记录，无法设置离校方式'
      });
    }

    if (attendanceRecord.arrival_status !== 'present') {
      return res.status(400).json({
        success: false,
        message: '学生今日未正常入园，无法设置离校方式'
      });
    }

    // 更新离校方式
    await executeQuery(`
      UPDATE attendance_records 
      SET departure_method = ?, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `, [departure_method, attendanceRecord.id]);

    // 发送WebSocket通知
    emitStudentStatusUpdate({
      studentId: student_id,
      classId: studentInfo.class_id,
      departure_method: departure_method,
      timestamp: timestamp,
      type: 'departure_method_set'
    });

    res.json({
      success: true,
      message: '离校方式设置成功',
      data: {
        student_id: student_id,
        student_name: studentInfo.name,
        departure_method: departure_method,
        timestamp: timestamp
      }
    });

  } catch (error) {
    console.error('设置离校方式错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 离班接口
router.post('/leave-class', [
  authenticateToken,
  requireTeacher,
  body('student_id').isInt().withMessage('学生ID必须是整数')
], async (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        message: '输入验证失败',
        errors: errors.array()
      });
    }

    const { student_id } = req.body;
    const date = new Date().toISOString().split('T')[0];
    const timestamp = new Date();
    const userRole = req.user.role;

    // 获取学生信息
    const studentInfo = await getOne(`
      SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      WHERE s.id = ?
    `, [student_id]);

    if (!studentInfo) {
      return res.status(404).json({
        success: false,
        message: '学生不存在'
      });
    }

    // 教师权限检查
    if (userRole === 'teacher') {
      const hasPermission = await getOne(`
        SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
      `, [req.user.id, studentInfo.class_id]);

      if (!hasPermission) {
        return res.status(403).json({
          success: false,
          message: '无权为该学生操作离班'
        });
      }
    }

    // 检查今日考勤记录
    const attendanceRecord = await getOne(`
      SELECT id, arrival_status, departure_status, departure_method FROM attendance_records 
      WHERE student_id = ? AND attendance_date = ?
    `, [student_id, date]);

    if (!attendanceRecord) {
      return res.status(400).json({
        success: false,
        message: '学生今日未入园，无法离班'
      });
    }

    if (attendanceRecord.arrival_status !== 'present') {
      return res.status(400).json({
        success: false,
        message: '学生今日未正常入园，无法离班'
      });
    }

    if (!attendanceRecord.departure_method) {
      return res.status(400).json({
        success: false,
        message: '请先设置离校方式'
      });
    }

    // 检查离园时间 - 所有离班操作都需要在离园时间之后
    const departureTime = await getAttendanceSetting('departure_time');
    const currentTime = new Date();
    const [hours, minutes] = departureTime.split(':');
    const departureDateTime = new Date();
    departureDateTime.setHours(parseInt(hours), parseInt(minutes), 0, 0);
    
    if (currentTime < departureDateTime) {
      return res.status(400).json({
        success: false,
        message: `离园时间未到，请在${departureTime}后再操作`
      });
    }

    let newDepartureStatus;
    let statusMessage;
    
    if (attendanceRecord.departure_method === 'parent_pickup' || attendanceRecord.departure_method === 'bus') {
      // 家长接领或校车直接离校
      newDepartureStatus = 'left_school';
      statusMessage = '学生已离校';
    } else if (attendanceRecord.departure_method === 'after_school' || attendanceRecord.departure_method === 'special_course') {
      // 托班或特色课程需要先离班
      newDepartureStatus = 'class_left';
      statusMessage = '学生已离班';
    }

    // 更新离班状态
    await executeQuery(`
      UPDATE attendance_records 
      SET departure_status = ?, departure_timestamp = ?, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `, [newDepartureStatus, timestamp, attendanceRecord.id]);

    // 发送WebSocket通知
    emitStudentStatusUpdate({
      studentId: student_id,
      classId: studentInfo.class_id,
      departure_status: newDepartureStatus,
      departure_method: attendanceRecord.departure_method,
      timestamp: timestamp,
      type: 'leave_class'
    });

    res.json({
      success: true,
      message: statusMessage,
      data: {
        student_id: student_id,
        student_name: studentInfo.name,
        departure_status: newDepartureStatus,
        departure_method: attendanceRecord.departure_method,
        timestamp: timestamp
      }
    });

  } catch (error) {
    console.error('离班操作失败:', error);
    res.status(500).json({
      success: false,
      message: '离班操作失败'
    });
  }
});

// 进班接口（课后教师使用）
router.post('/enter-class', [
  authenticateToken,
  requireTeacher,
  body('student_id').isInt().withMessage('学生ID必须是整数')
], async (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        message: '输入验证失败',
        errors: errors.array()
      });
    }

    const { student_id } = req.body;
    const date = new Date().toISOString().split('T')[0];
    const timestamp = new Date();
    const teacherId = req.user.id;

    // 获取学生信息
    const studentInfo = await getOne(`
      SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      WHERE s.id = ?
    `, [student_id]);

    if (!studentInfo) {
      return res.status(404).json({
        success: false,
        message: '学生不存在'
      });
    }

    // 检查教师是否有权限（课后教师）
    const hasPermission = await getOne(`
      SELECT 1 FROM after_school_classes 
      WHERE teacher_id = ? AND class_id = ?
    `, [teacherId, studentInfo.class_id]);

    if (!hasPermission) {
      return res.status(403).json({
        success: false,
        message: '无权为该学生操作进班'
      });
    }

    // 检查今日考勤记录
    const attendanceRecord = await getOne(`
      SELECT id, arrival_status, departure_status, departure_method FROM attendance_records 
      WHERE student_id = ? AND attendance_date = ?
    `, [student_id, date]);

    if (!attendanceRecord) {
      return res.status(400).json({
        success: false,
        message: '学生今日未入园，无法进班'
      });
    }

    if (attendanceRecord.arrival_status !== 'present') {
      return res.status(400).json({
        success: false,
        message: '学生今日未正常入园，无法进班'
      });
    }

    if (attendanceRecord.departure_status !== 'class_left') {
      return res.status(400).json({
        success: false,
        message: '学生未离班，无法进班'
      });
    }

    if (attendanceRecord.departure_method !== 'after_school' && attendanceRecord.departure_method !== 'special_course') {
      return res.status(400).json({
        success: false,
        message: '只有托班或特色课程的学生才能进班'
      });
    }

    // 更新进班状态
    await executeQuery(`
      UPDATE attendance_records 
      SET departure_status = 'class_entered', updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `, [attendanceRecord.id]);

    // 发送WebSocket通知
    emitStudentStatusUpdate({
      studentId: student_id,
      classId: studentInfo.class_id,
      departure_status: 'class_entered',
      departure_method: attendanceRecord.departure_method,
      timestamp: timestamp,
      type: 'enter_class'
    });

    res.json({
      success: true,
      message: '学生已进班',
      data: {
        student_id: student_id,
        student_name: studentInfo.name,
        departure_status: 'class_entered',
        departure_method: attendanceRecord.departure_method,
        timestamp: timestamp
      }
    });

  } catch (error) {
    console.error('进班操作失败:', error);
    res.status(500).json({
      success: false,
      message: '进班操作失败'
    });
  }
});

// 课后离班接口
router.post('/leave-after-school-class', [
  authenticateToken,
  requireTeacher,
  body('student_id').isInt().withMessage('学生ID必须是整数')
], async (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        message: '输入验证失败',
        errors: errors.array()
      });
    }

    const { student_id } = req.body;
    const date = new Date().toISOString().split('T')[0];
    const timestamp = new Date();
    const teacherId = req.user.id;

    // 获取学生信息
    const studentInfo = await getOne(`
      SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      WHERE s.id = ?
    `, [student_id]);

    if (!studentInfo) {
      return res.status(404).json({
        success: false,
        message: '学生不存在'
      });
    }

    // 检查教师是否有权限（课后教师）
    const hasPermission = await getOne(`
      SELECT 1 FROM after_school_classes 
      WHERE teacher_id = ? AND class_id = ?
    `, [teacherId, studentInfo.class_id]);

    if (!hasPermission) {
      return res.status(403).json({
        success: false,
        message: '无权为该学生操作离班'
      });
    }

    // 检查今日考勤记录
    const attendanceRecord = await getOne(`
      SELECT id, arrival_status, departure_status, departure_method FROM attendance_records 
      WHERE student_id = ? AND attendance_date = ?
    `, [student_id, date]);

    if (!attendanceRecord) {
      return res.status(400).json({
        success: false,
        message: '学生今日未入园，无法操作'
      });
    }

    if (attendanceRecord.departure_status !== 'class_entered') {
      return res.status(400).json({
        success: false,
        message: '学生未进班，无法离班'
      });
    }

    if (attendanceRecord.departure_method !== 'after_school' && attendanceRecord.departure_method !== 'special_course') {
      return res.status(400).json({
        success: false,
        message: '只有托班或特色课程的学生才能操作'
      });
    }

    // 更新为已离校状态
    await executeQuery(`
      UPDATE attendance_records 
      SET departure_status = 'left_school', departure_timestamp = ?, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `, [timestamp, attendanceRecord.id]);

    // 发送WebSocket通知
    emitStudentStatusUpdate({
      studentId: student_id,
      classId: studentInfo.class_id,
      departure_status: 'left_school',
      departure_method: attendanceRecord.departure_method,
      timestamp: timestamp,
      type: 'left_school'
    });

    res.json({
      success: true,
      message: '学生已离校',
      data: {
        student_id: student_id,
        student_name: studentInfo.name,
        departure_status: 'left_school',
        departure_method: attendanceRecord.departure_method,
        timestamp: timestamp
      }
    });

  } catch (error) {
    console.error('课后离班操作失败:', error);
    res.status(500).json({
      success: false,
      message: '课后离班操作失败'
    });
  }
});

// 获取校车名单（司机使用）
router.get('/bus-list', authenticateToken, requireDriver, async (req, res) => {
  try {
    const { date = new Date().toISOString().split('T')[0] } = req.query;
    const driverId = req.user.id;

    // 获取司机的校车信息
    const driverInfo = await getOne(`
      SELECT u.bus_no FROM users u WHERE u.id = ? AND u.role = 'driver'
    `, [driverId]);

    if (!driverInfo || !driverInfo.bus_no) {
      return res.json({
        success: true,
        data: {
          date,
          students: [],
          total: 0
        }
      });
    }

    // 获取该校车的学生名单
    const busList = await executeQuery(`
      SELECT 
        ar.id as record_id,
        s.id, s.student_no, s.name,
        c.name as class_name, c.grade,
        ar.arrival_timestamp, TIME(ar.arrival_timestamp) as arrival_time,
        ar.departure_timestamp, TIME(ar.departure_timestamp) as departure_time,
        ar.notes,
        ar.departure_status,
        ar.departure_method
      FROM attendance_records ar
      INNER JOIN students s ON ar.student_id = s.id
      INNER JOIN classes c ON s.class_id = c.id
      WHERE ar.attendance_date = ? 
        AND ar.arrival_status = 'present' 
        AND ar.departure_method = 'bus'
        AND s.assigned_bus_id = ?
      ORDER BY c.grade, c.name, s.student_no
    `, [date, driverId]);

    res.json({
      success: true,
      data: {
        date,
        bus_no: driverInfo.bus_no,
        students: busList,
        total: busList.length
      }
    });

  } catch (error) {
    console.error('获取校车名单错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 获取班级考勤详情
router.get('/class-attendance/:classId', authenticateToken, requireTeacher, async (req, res) => {
  try {
    const { classId } = req.params;
    const { date = new Date().toISOString().split('T')[0] } = req.query;
    const userRole = req.user.role;

    // 教师权限检查
    if (userRole === 'teacher') {
      const hasPermission = await getOne(`
        SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
      `, [req.user.id, classId]);

      if (!hasPermission) {
        return res.status(403).json({
          success: false,
          message: '无权访问该班级考勤信息'
        });
      }
    }

    // 获取班级信息
    const classInfo = await getOne(`
      SELECT id, name, grade FROM classes WHERE id = ?
    `, [classId]);

    if (!classInfo) {
      return res.status(404).json({
        success: false,
        message: '班级不存在'
      });
    }

    // 获取学生考勤详情
    const students = await executeQuery(`
      SELECT 
        s.id, s.student_no, s.name, s.qr_code,
        ar.id as attendance_id,
        ar.attendance_date,
        ar.arrival_status,
        ar.arrival_timestamp, TIME(ar.arrival_timestamp) as arrival_time,
        ar.departure_status,
        ar.departure_method,
        ar.departure_timestamp, TIME(ar.departure_timestamp) as departure_time,
        ar.notes,
        -- 计算在园时长
        CASE 
          WHEN ar.arrival_timestamp IS NOT NULL AND ar.departure_timestamp IS NOT NULL 
          THEN TIMESTAMPDIFF(MINUTE, ar.arrival_timestamp, ar.departure_timestamp)
          ELSE NULL 
        END as duration_minutes,
        ar.created_at, ar.updated_at
      FROM students s
      LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
      WHERE s.class_id = ?
      ORDER BY s.student_no
    `, [date, classId]);

    // 统计信息
    const stats = {
      total: students.length,
      present: students.filter(s => s.arrival_status === 'present').length,
      leave: students.filter(s => s.arrival_status === 'leave').length,
      abnormal: students.filter(s => s.arrival_status === 'abnormal').length,
      absent: students.filter(s => !s.arrival_status || s.arrival_status === 'absent').length,
      departure_stats: {
        not_set: students.filter(s => s.arrival_status === 'present' && !s.departure_status).length,
        left_school: students.filter(s => s.departure_status === 'left_school').length,
        class_left: students.filter(s => s.departure_status === 'class_left').length,
        class_entered: students.filter(s => s.departure_status === 'class_entered').length
      },
      departure_method_stats: {
        parent_pickup: students.filter(s => s.departure_method === 'parent_pickup').length,
        bus: students.filter(s => s.departure_method === 'bus').length,
        after_school: students.filter(s => s.departure_method === 'after_school').length,
        special_course: students.filter(s => s.departure_method === 'special_course').length
      }
    };

    stats.attendance_rate = stats.total > 0 ? ((stats.present / stats.total) * 100).toFixed(2) : 0;

    res.json({
      success: true,
      data: {
        date,
        class: classInfo,
        students,
        statistics: stats
      }
    });

  } catch (error) {
    console.error('获取班级考勤详情错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 获取考勤历史汇总
router.get('/history-summary', authenticateToken, async (req, res) => {
  try {
    const {
      startDate = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000).toISOString().split('T')[0],
      endDate = new Date().toISOString().split('T')[0],
      classId 
    } = req.query;
    const userRole = req.user.role;

    let whereClause = 'WHERE ar.attendance_date BETWEEN ? AND ?';
    let params = [startDate, endDate];

    // 教师只能查看自己负责的班级
    if (userRole === 'teacher') {
      const teacherClasses = await executeQuery(`
        SELECT class_id FROM teacher_classes WHERE teacher_id = ?
      `, [req.user.id]);

      if (teacherClasses.length === 0) {
        return res.json({
          success: true,
          data: { summary: [] }
        });
      }

      const classIds = teacherClasses.map(tc => tc.class_id);
      whereClause += ` AND c.id IN (${classIds.map(() => '?').join(',')})`;
      params.push(...classIds);
    }

    if (classId) {
      whereClause += ' AND c.id = ?';
      params.push(classId);
    }

    const summary = await executeQuery(`
      SELECT 
        ar.attendance_date,
        c.id as class_id, c.name as class_name, c.grade,
        COUNT(DISTINCT s.id) as total_students,
        COUNT(CASE WHEN ar.arrival_status = 'present' THEN 1 END) as present_count,
        COUNT(CASE WHEN ar.arrival_status = 'leave' THEN 1 END) as leave_count,
        COUNT(CASE WHEN ar.arrival_status = 'abnormal' THEN 1 END) as abnormal_count,
        COUNT(CASE WHEN ar.arrival_status = 'absent' OR ar.arrival_status IS NULL THEN 1 END) as absent_count,
        ROUND(COUNT(CASE WHEN ar.arrival_status = 'present' THEN 1 END) * 100.0 / NULLIF(COUNT(DISTINCT s.id), 0), 2) as attendance_rate,
        -- 离校方式统计
        COUNT(CASE WHEN ar.departure_method = 'bus' THEN 1 END) as bus_count,
        COUNT(CASE WHEN ar.departure_method = 'parent_pickup' THEN 1 END) as parent_pickup_count,
        COUNT(CASE WHEN ar.departure_method = 'after_school' THEN 1 END) as after_school_count,
        COUNT(CASE WHEN ar.departure_method = 'special_course' THEN 1 END) as special_course_count,
        -- 离校状态统计
        COUNT(CASE WHEN ar.departure_status = 'left_school' THEN 1 END) as left_school_count,
        COUNT(CASE WHEN ar.departure_status = 'class_left' THEN 1 END) as class_left_count,
        COUNT(CASE WHEN ar.departure_status = 'class_entered' THEN 1 END) as class_entered_count,
        -- 时间统计
        MIN(ar.arrival_timestamp) as earliest_arrival,
        MAX(ar.arrival_timestamp) as latest_arrival,
        AVG(CASE WHEN ar.arrival_timestamp IS NOT NULL 
          THEN TIME_TO_SEC(TIME(ar.arrival_timestamp)) END) / 60 as avg_arrival_minutes
      FROM classes c
      LEFT JOIN students s ON c.id = s.class_id
      LEFT JOIN attendance_records ar ON s.id = ar.student_id 
      ${whereClause}
      GROUP BY ar.attendance_date, c.id, c.name, c.grade
      ORDER BY ar.attendance_date DESC, c.grade, c.name
    `, params);

    res.json({
      success: true,
      data: {
        startDate,
        endDate,
        summary
      }
    });

  } catch (error) {
    console.error('获取考勤历史汇总错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

export default router;
